#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into zx_dm.dm_intention
with tmp as(
select
       substring (create_date_time,1,10) as date_time,
       year(cast (create_date_time as timestamp)) as year ,
       month(cast (create_date_time as timestamp)) as month ,
       day (cast (create_date_time as timestamp)) as day ,
       hour (cast (create_date_time as timestamp)) as hour
from zx_dwb.dwb_intention_detail),
tmp2 as(
select

    -- 统计日期
   '${dt}' as date_time,

   tmp.year,
   tmp.month,
   tmp.day,
   tmp.hour,

    -- 时间维度      year、month、day、hour
   case when grouping(tmp.year, tmp.month, tmp.day, tmp.hour) = 0
      then 'hour'
      when grouping(tmp.year, tmp.month, tmp.day) = 0
      then 'day'
      when grouping(tmp.year, tmp.month) = 0
      then 'month'
      when grouping(tmp.year) = 0
      then 'year'
      end as time_type,
    origin_type,
    itcast_school_id,
    school_name,
    itcast_subject_id,
    subject_name,
    origin_channel,
    tdepart_id,
    dept_name,
    area,
    clue_state,
    group_type,
    case when grouping (area) = 0
            then 'area' -- 地区
         when grouping (itcast_subject_id) = 0
            then 'subject' -- 学科
         when grouping (itcast_school_id) = 0
            then 'school' -- 校区
         when grouping (origin_channel) = 0
            then 'origin' -- 渠道
         when grouping (tdepart_id) = 0
            then 'dept' -- 部门
         else 'all' end as new_group_type, -- 新分组标记

    -- 计算指标
    sum (intention_cnt) as intention_amt, -- 意向个数
    sum (valid_clue_cnt) as valid_clus_amt -- 线索个数

from zx_dws.dws_intention_daycount did
left join tmp on did.date_time = tmp.date_time
group by grouping sets (
    -- 年统计
    (tmp.year,group_type),
    (tmp.year,origin_type,clue_state,group_type),
    (tmp.year,area,origin_type,clue_state,group_type),
    (tmp.year,itcast_subject_id,subject_name,origin_type,clue_state,group_type),
    (tmp.year,itcast_school_id,school_name,origin_type,clue_state,group_type),
    (tmp.year,origin_channel,origin_type,clue_state,group_type),
    (tmp.year,tdepart_id,dept_name,origin_type,clue_state,group_type),

    -- 月统计
    (tmp.year,tmp.month,group_type),
    (tmp.year,tmp.month,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,area,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,itcast_subject_id,subject_name,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,itcast_school_id,school_name,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,origin_channel,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tdepart_id,dept_name,origin_type,clue_state,group_type),

    -- 天统计
    (tmp.year,tmp.month,tmp.day,group_type),
    (tmp.year,tmp.month,tmp.day,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,area,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,itcast_subject_id,subject_name,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,itcast_school_id,school_name,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,origin_channel,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,tdepart_id,dept_name,origin_type,clue_state,group_type),

    -- 时统计
    (tmp.year,tmp.month,tmp.day,tmp.hour,group_type),
    (tmp.year,tmp.month,tmp.day,tmp.hour,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,tmp.hour,area,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,tmp.hour,itcast_subject_id,subject_name,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,tmp.hour,itcast_school_id,school_name,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,tmp.hour,origin_channel,origin_type,clue_state,group_type),
    (tmp.year,tmp.month,tmp.day,tmp.hour,tdepart_id,dept_name,origin_type,clue_state,group_type))
)
select * from tmp2
where group_type = new_group_type;
"